package org.jeecg.modules.online.cgreport.controller;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang.StringUtils;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.constant.CommonConstant;
import org.jeecg.common.system.api.ISysBaseAPI;
import org.jeecg.common.system.query.QueryGenerator;
import org.jeecg.common.system.vo.DynamicDataSourceModel;
import org.jeecg.common.util.SqlInjectionUtil;
import org.jeecg.modules.online.cgform.util.CgformUtil;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportHead;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportItem;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportParam;
import org.jeecg.modules.online.cgreport.model.OnlCgreportModel;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportHeadService;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportItemService;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportParamService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import lombok.extern.slf4j.Slf4j;

/**
 * @Title: Controller
 * @Description: 在线报表配置
 * @Author: jeecg-boot
 * @Date: 2019-03-08
 * @Version: V1.0
 */
@RestController("onlCgreportHeadController")
@RequestMapping("/online/cgreport/head")
@Slf4j
public class OnlCgreportHeadController {

	@Autowired
	private ISysBaseAPI sysBaseAPI;
	@Autowired
	private IOnlCgreportHeadService onlCgreportHeadService;
	@Autowired
	private IOnlCgreportParamService onlCgreportParamService;
	@Autowired
	private IOnlCgreportItemService onlCgreportItemService;

	/**
	 * 动态数据源解析SQL获取字段和参数
	 *
	 * @param dbKey 数据源 key，传 code
	 * @param sql   要解析的 sql
	 * @return
	 */
	@GetMapping(value = "/parseSql")
	public Result<?> parseSql(@RequestParam(name = "sql") String sql, @RequestParam(name = "dbKey", required = false) String dbKey) {

		if (StringUtils.isNotBlank(dbKey)) {
			// 查询数据源是否存在
			DynamicDataSourceModel dbSource = sysBaseAPI.getDynamicDbSourceByCode(dbKey);
			if (dbSource == null) {
				return Result.error("数据源不存在");
			}
		}

		Map<String, Object> resultJson = new HashMap<String, Object>();
		//返回SQL解析字段
		List<OnlCgreportItem> onlCgreportItemList = new ArrayList<OnlCgreportItem>();
		//返回SQL解析参数
		List<OnlCgreportParam> onlCgreportParamList = new ArrayList<OnlCgreportParam>();
		List<String> fields = null;
		List<String> params = null;
		try {
			log.info("Online报表，sql解析："+sql);
			sysBaseAPI.addLog("Online报表，sql解析："+sql, CommonConstant.LOG_TYPE_2, 2);
			//校验防止SQL注入，禁止操作表和数据，只允许查询操作
			SqlInjectionUtil.specialFilterContentForOnlineReport(sql);
			fields = onlCgreportHeadService.getSqlFields(sql, dbKey);
			params = onlCgreportHeadService.getSqlParams(sql);
			
			int i = 1;
			for(String f:fields) {
				OnlCgreportItem t = new OnlCgreportItem();
				//默认转为小写
				t.setFieldName(f.toLowerCase());
				t.setFieldTxt(f);
				t.setIsShow(1);
				t.setOrderNum(i);
				t.setId(CgformUtil.randomId());
				t.setFieldType("String");
				onlCgreportItemList.add(t);
				i++;
			}
			
			for(String p:params) {
				OnlCgreportParam param = new OnlCgreportParam();
				param.setParamName(p);
				param.setParamTxt(p);
				onlCgreportParamList.add(param);
			}
			resultJson.put("fields", onlCgreportItemList);
			resultJson.put("params", onlCgreportParamList);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			String msg = "解析失败，";
			int i = e.getMessage().indexOf("Connection refused: connect");
			if (i != -1) {// 非链接异常
				msg += "数据源连接失败.";
			} else if (e.getMessage().indexOf("值可能存在SQL注入风险")!=-1){
				msg += "SQL可能存在SQL注入风险.";
			}else if (e.getMessage().indexOf("该报表sql没有数据")!=-1){
				msg += "报表sql查询数据为空，无法解析字段.";
			}else if (e.getMessage().indexOf("SqlServer不支持SQL内排序")!=-1){
				msg += "SqlServer不支持SQL内排序.";
			}else {
				msg += "SQL语法错误.";
			}
			return Result.error(msg);
		}
		return Result.ok(resultJson);
	}

	/**
	 * 分页列表查询
	 * 
	 * @param onlCgreportHead
	 * @param pageNo
	 * @param pageSize
	 * @param req
	 * @return
	 */
	@GetMapping(value = "/list")
	public Result<IPage<OnlCgreportHead>> queryPageList(OnlCgreportHead onlCgreportHead, @RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo, @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize, HttpServletRequest req) {
		Result<IPage<OnlCgreportHead>> result = new Result<IPage<OnlCgreportHead>>();
		QueryWrapper<OnlCgreportHead> queryWrapper = QueryGenerator.initQueryWrapper(onlCgreportHead, req.getParameterMap());
		Page<OnlCgreportHead> page = new Page<OnlCgreportHead>(pageNo, pageSize);
		IPage<OnlCgreportHead> pageList = onlCgreportHeadService.page(page, queryWrapper);
		result.setSuccess(true);
		result.setResult(pageList);
		return result;
	}

	/**
	 * 添加
	 *
	 * @param values
	 * @return
	 */
	@PostMapping(value = "/add")
	public Result<?> add(@RequestBody OnlCgreportModel values) {
		Result<OnlCgreportHead> result = new Result<OnlCgreportHead>();
		try {
			String mainId = CgformUtil.randomId();

			OnlCgreportHead head = values.getHead();
			List<OnlCgreportParam> params = values.getParams();
			List<OnlCgreportItem> items = values.getItems();

			head.setId(mainId);
			for (OnlCgreportParam param : params) {
				param.setId(null);
				param.setCgrheadId(mainId);
			}
			for (OnlCgreportItem item : items) {
				item.setId(null);
				//字段默认保存为小写(为了兼容各种数据库)
				item.setFieldName(item.getFieldName().trim().toLowerCase());
				item.setCgrheadId(mainId);
			}

			onlCgreportHeadService.save(head);
			onlCgreportParamService.saveBatch(params);
			onlCgreportItemService.saveBatch(items);

			result.success("添加成功！");
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			result.error500("操作失败");
		}
		return result;
	}

	/**
	 * 编辑保存全部操作
	 */
	@PutMapping(value = "/editAll")
	public Result<?> editAll(@RequestBody OnlCgreportModel values) {
		try {
			return onlCgreportHeadService.editAll(values);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return Result.error("操作失败");
		}
	}

//	/**
//	 * 编辑（作废接口）
//	 *
//	 * @param onlCgreportHead
//	 * @return
//	 */
//	@PutMapping(value = "/edit")
//	public Result<OnlCgreportHead> edit(@RequestBody OnlCgreportHead onlCgreportHead) {
//		Result<OnlCgreportHead> result = new Result<OnlCgreportHead>();
//		OnlCgreportHead onlCgreportHeadEntity = onlCgreportHeadService.getById(onlCgreportHead.getId());
//		if (onlCgreportHeadEntity == null) {
//			result.error500("未找到对应实体");
//		} else {
//			boolean ok = onlCgreportHeadService.updateById(onlCgreportHead);
//			if (ok) {
//				result.success("修改成功!");
//			}
//		}
//
//		return result;
//	}

	/**
	 * 通过id删除
	 * 
	 * @param id
	 * @return
	 */
	@DeleteMapping(value = "/delete")
	public Result<?> delete(@RequestParam(name = "id", required = true) String id) {
		return onlCgreportHeadService.delete(id);
	}

	/**
	 * 批量删除
	 * 
	 * @param ids
	 * @return
	 */
	@DeleteMapping(value = "/deleteBatch")
	public Result<?> deleteBatch(@RequestParam(name = "ids", required = true) String ids) {
		return onlCgreportHeadService.bathDelete(ids.split(","));
	}

	/**
	 * 通过id查询
	 * 
	 * @param id
	 * @return
	 */
	@GetMapping(value = "/queryById")
	public Result<OnlCgreportHead> queryById(@RequestParam(name = "id", required = true) String id) {
		Result<OnlCgreportHead> result = new Result<OnlCgreportHead>();
		OnlCgreportHead onlCgreportHead = onlCgreportHeadService.getById(id);
		result.setResult(onlCgreportHead);
		return result;
	}

}
